﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using MDL;

namespace DAL
{
    public class DAL_RoomType
    {
        /// <summary>
        /// 查询所有房间的类型
        /// </summary>
        /// <returns>所有房间列表</returns>
        public static List<RoomType> SelectRoomType()
        {
            string sql = "select * from RoomType";
            List<RoomType> list = new List<RoomType>();
            using (SqlDataReader dr = DBHelper.白道(sql, null, CommandType.Text))
            {
                while (dr.Read())
                {
                    RoomType r = new RoomType();
                    r.RTID = Convert.ToInt32(dr[0]);
                    r.RTName = dr[1].ToString();
                    r.RTConsume = Convert.ToInt32(dr[2]);
                    r.RTIsDisCount = Convert.ToInt32(dr[3]);
                    r.RTMount = Convert.ToInt32(dr[4]);
                    list.Add(r);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据房间类型查最低消费
        /// </summary>
        /// <param name="name">房间名</param>
        /// <returns>最低消费</returns>
        public static object SelectRoomTypeXiaoFei(string name)
        {
            string sql = "select RTConsume from RoomType where RTName = @name";
            SqlParameter[] sprs = { new SqlParameter("@name", name) };
            return DBHelper.单值(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 下拉绑定房间类型
        /// </summary>
        /// <returns>房间类型</returns>
        public static List<RoomType> BangDinRoomType()
        {
            string sql = "select RTID,RTName from RoomType";
            List<RoomType> list = new List<RoomType>();
            using (SqlDataReader dr = DBHelper.白道(sql, null, CommandType.Text))
            {
                while (dr.Read())
                {
                    RoomType r = new RoomType();
                    r.RTID = Convert.ToInt32(dr[0]);
                    r.RTName = dr[1].ToString();
                    list.Add(r);
                }
            }
            return list;
        }
        /// <summary>
        /// 查询是否已有相同的房间类型名称
        /// </summary>
        /// <param name="name">要查询的房间类型名称</param>
        /// <returns>是否有</returns>
        public static List<RoomType> GetRoomType(string name)
        {
            string sql = "select * from RoomType where RTName = @name";
            SqlParameter[] sprs = { new SqlParameter("@name", name) };
            List<RoomType> list = new List<RoomType>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs, CommandType.Text))
            {
                while (dr.Read())
                {
                    RoomType p = new RoomType();
                    p.RTName = dr[1].ToString();
                    //p.ProductJP = dr[3].ToString();
                    list.Add(p);
                }
            }
            return list;
        }
        /// <summary>
        /// 增加房间类型
        /// </summary>
        /// <param name="name">房间名称</param>
        /// <param name="xf">最低消费</param>
        /// <param name="dz">是否打折</param>
        /// <param name="rs">容纳人数</param>
        /// <returns>受影响的行数</returns>
        public static int AddRoomType(string name,int xf,int dz,int rs)
        {
            string sql = "insert into RoomType values(@name,@xf,@dz,@rs)";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",name),
                                  new SqlParameter("@xf",xf),
                                  new SqlParameter("@dz",dz),
                                  new SqlParameter("@rs",rs)
                                  };
           return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 删除房间类型
        /// </summary>
        /// <param name="name">根据房间名称</param>
        /// <returns>受影响的行数</returns>
        public static int DeleteRoomType(string name)
        {
            string sql = "delete RoomType where RTName = @name";
            SqlParameter[] sprs = { new SqlParameter("@name",name)};
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 修改房间类型
        /// </summary>
        /// <param name="name">房间名称</param>
        /// <param name="xf">最低消费</param>
        /// <param name="dz">是否打折</param>
        /// <param name="rs">容纳人数</param>
        /// <param name="name1">根据房间名称</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateRoomType(string name,int xf,int dz,int rs,string name1)
        {
            string sql = @"update RoomType set RTName=@name,RTConsume=@xf,
                            RTIsDisCount=@dz,RTMount=@rs where RTName = @name1";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",name),
                                  new SqlParameter("@xf",xf),
                                  new SqlParameter("@dz",dz),
                                  new SqlParameter("@rs",rs),
                                  new SqlParameter("@name1",name1)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 根据餐台ID查询房间名称和最低消费
        /// </summary>
        /// <param name="ID">餐台ID</param>
        /// <returns>房间名称和最低消费</returns>
        public static List<RoomType> SelectName(int ID)
        {
            string sql = @"select t.*,r.RTName,r.RTConsume from Tables t inner join 
                            RoomType r on t.RTID=r.RTID where TableID = @ID";
            SqlParameter[] sprs = { new SqlParameter("@ID",ID)};
            List<RoomType> list = new List<RoomType>();
            using (SqlDataReader dr = DBHelper.白道(sql,sprs,CommandType.Text))
            {
                while (dr.Read())
                {
                    RoomType r = new RoomType();
                    r.RTName = dr[5].ToString();
                    r.RTConsume = Convert.ToInt32(dr[6]);
                    list.Add(r);
                }
            }
            return list;
        }
    }
}
